using System;
using System.Collections.Generic;
using System.Text;

namespace SchemaProvider
{
    public static class Sql2005CommandUtility
    {
        //public static string ListTableNames()
        //{
        //    return "select TableName=[name] from sysobjects where xtype = 'U' and [name] <> 'sysdiagrams' and year(crdate)>2000 order by [name]";
        //}

        public static string ListTableNames()
        {
            return "select s.name+'.'+t.name as TableName from sys.tables t,sys.schemas s where t.schema_id=s.schema_id order by s.name,t.name";
        }

        //public static string ListViewNames()
        //{
        //    return "select TableName=[name] from sysobjects where xtype = 'V' and year(crdate)>2000 order by [name]";
        //}

        public static string ListViewNames()
        {
            return "select s.name+'.'+v.name as TableName from sys.views v,sys.schemas s where v.schema_id=s.schema_id order by s.name,v.name";
        }

        public static string ListPrimaryKeys()
        {
            return "SELECT TableName=T.name,PrimaryKey=C.name FROM syscolumns C INNER JOIN " +
                "sysobjects T ON C.id=T.id and T.xtype='U' AND T.name<>'dtproperties' " +
                "WHERE EXISTS(SELECT 1 FROM sysobjects WHERE xtype='PK' AND name IN( " +
                "SELECT name FROM sysindexes WHERE indid IN( SELECT indid FROM sysindexkeys WHERE id=C.id AND colid=C.colid ))) ORDER BY T.name ";
        }

        public static string ListForeignKey()
        {
            return "SELECT TableName=T.name,ForeignKey=FC.name,ReferenceTableName=R.name,ReferenceKey=RC.name FROM sysforeignkeys F LEFT JOIN " +
                    "sysobjects T ON F.fkeyid=T.id LEFT JOIN sysobjects R ON F.rkeyid=R.id LEFT JOIN " +
                    "syscolumns FC ON F.fkeyid=FC.id AND F.fkey=FC.colid LEFT JOIN syscolumns RC ON F.rkeyid=RC.id AND F.rkey=RC.colid " +
                    "WHERE T.xtype='U' AND R.xtype='U'";
        }

        public static string ListIdentities()
        {
            return "select IdentityName=a.name,TableName=b.name  from  syscolumns  a left join sysobjects b on a.id=b.id where COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1";
        }

        public static string ListProcedures()
        {
            return "select a.name ProcedureName,c.name ParameterName,c.length Length,c.isoutparam IsOutput,d.name ParameterType " +
                    "from sysobjects a " +
                    "left join syscomments b on a.id = b.id " +
                    "left join syscolumns c on a.id = c.id " +
                    "left join systypes d on c.xtype = d.xtype " +
                    "where (a.type='P'or a.type='FN') and a.status<>1 and d.name<>'sysname' order by a.name asc";
        }
    }
}
